from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json
import pytz

db = SQLAlchemy()

# 北京时区
beijing_tz = pytz.timezone('Asia/Shanghai')

def get_beijing_time():
    """获取北京时间"""
    return datetime.now(beijing_tz)

class Subscription(db.Model):
    """订阅表"""
    __tablename__ = 'subscriptions'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    url = db.Column(db.Text, nullable=False)
    type = db.Column(db.String(50), nullable=False)
    site = db.Column(db.String(50), nullable=True)  # 新增：订阅网站字段
    download_directory = db.Column(db.String(500))
    parent_folder_id = db.Column(db.String(64), nullable=True)
    is_active = db.Column(db.Boolean, default=False)
    auto_download = db.Column(db.Boolean, default=False)
    latest_download_urls = db.Column(db.Text, default='[]')  # JSON格式存储
    created_at = db.Column(db.DateTime, default=get_beijing_time)
    updated_at = db.Column(db.DateTime, default=get_beijing_time, onupdate=get_beijing_time)
    
    # 关联关系
    download_tasks = db.relationship('DownloadTask', backref='subscription', lazy=True, cascade='all, delete-orphan')
    crawl_logs = db.relationship('CrawlLog', backref='subscription', lazy=True, cascade='all, delete-orphan')
    
    def __init__(self, **kwargs):
        super().__init__(**kwargs)
    
    def get_latest_download_urls(self):
        """获取最新下载地址列表"""
        try:
            return json.loads(self.latest_download_urls or '[]')
        except:
            return []
    
    def set_latest_download_urls(self, urls):
        """设置最新下载地址列表"""
        self.latest_download_urls = json.dumps(urls, ensure_ascii=False)
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'url': self.url,
            'type': self.type,
            'site': self.site,  # 新增
            'download_directory': self.download_directory,
            'parent_folder_id': self.parent_folder_id,
            'is_active': self.is_active,
            'auto_download': self.auto_download,
            'latest_download_urls': self.get_latest_download_urls(),
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class DownloadTask(db.Model):
    """下载任务表"""
    __tablename__ = 'download_tasks'
    
    id = db.Column(db.Integer, primary_key=True)
    subscription_id = db.Column(db.Integer, db.ForeignKey('subscriptions.id'), nullable=False)
    name = db.Column(db.String(200), nullable=False)
    url = db.Column(db.Text, nullable=False)
    directory = db.Column(db.String(500), nullable=False)
    parent_folder_id = db.Column(db.String(64))
    status = db.Column(db.String(20), default='waiting_create')  # waiting_create(等待创建), created_success(创建成功), created_failed(创建失败), task_duplicate(任务重复)
    created_at = db.Column(db.DateTime, default=get_beijing_time)
    updated_at = db.Column(db.DateTime, default=get_beijing_time, onupdate=get_beijing_time)
    
    # 关联关系 - 添加queue_items关系并设置级联删除
    queue_items = db.relationship('DownloadQueue', backref='task', lazy=True, cascade='all, delete-orphan')
    
    def to_dict(self):
        return {
            'id': self.id,
            'subscription_id': self.subscription_id,
            'name': self.name,
            'url': self.url,
            'directory': self.directory,
            'parent_folder_id': self.parent_folder_id,
            'status': self.status,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class CrawlLog(db.Model):
    """爬取日志表"""
    __tablename__ = 'crawl_logs'
    
    id = db.Column(db.Integer, primary_key=True)
    subscription_id = db.Column(db.Integer, db.ForeignKey('subscriptions.id'), nullable=False)
    crawl_count = db.Column(db.Integer, default=1)
    crawl_time = db.Column(db.DateTime, default=get_beijing_time)
    new_urls = db.Column(db.Text)  # JSON格式存储增量地址
    total_urls_count = db.Column(db.Integer, default=0)
    status = db.Column(db.String(20), default='success')  # success, failed
    error_message = db.Column(db.Text)
    
    def get_new_urls(self):
        """获取新增地址列表"""
        try:
            return json.loads(self.new_urls or '[]')
        except:
            return []
    
    def set_new_urls(self, urls):
        """设置新增地址列表"""
        self.new_urls = json.dumps(urls, ensure_ascii=False) if urls else None
    
    def to_dict(self):
        return {
            'id': self.id,
            'subscription_id': self.subscription_id,
            'crawl_count': self.crawl_count,
            'crawl_time': self.crawl_time.isoformat() if self.crawl_time else None,
            'new_urls': self.get_new_urls(),
            'total_urls_count': self.total_urls_count,
            'status': self.status,
            'error_message': self.error_message
        }

class SystemConfig(db.Model):
    """系统配置表"""
    __tablename__ = 'system_config'
    
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(100), unique=True, nullable=False)
    value = db.Column(db.Text, nullable=False)
    description = db.Column(db.String(200))
    updated_at = db.Column(db.DateTime, default=get_beijing_time, onupdate=get_beijing_time)
    
    def to_dict(self):
        return {
            'id': self.id,
            'key': self.key,
            'value': self.value,
            'description': self.description,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }
    
    @classmethod
    def get_config(cls, key, default=None):
        """获取配置值"""
        config = cls.query.filter_by(key=key).first()
        return config.value if config else default
    
    @classmethod
    def set_config(cls, key, value, description=None):
        """设置配置值"""
        config = cls.query.filter_by(key=key).first()
        if config:
            config.value = str(value)
            config.updated_at = get_beijing_time()
            if description:
                config.description = description
        else:
            config = cls(key=key, value=str(value), description=description)  # type: ignore
            db.session.add(config)
        db.session.commit()
        return config

class DownloadQueue(db.Model):
    """下载队列表"""
    __tablename__ = 'download_queue'
    
    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.Integer, db.ForeignKey('download_tasks.id', ondelete='CASCADE'), nullable=False)
    priority = db.Column(db.Integer, default=0)  # 优先级，数字越大优先级越高
    retry_count = db.Column(db.Integer, default=0)  # 重试次数
    max_retries = db.Column(db.Integer, default=3)  # 最大重试次数
    status = db.Column(db.String(20), default='queued')  # queued, processing, completed, failed
    error_message = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=get_beijing_time)
    updated_at = db.Column(db.DateTime, default=get_beijing_time, onupdate=get_beijing_time)
    
    # 关联关系 - 移除cascade设置，因为这是many-to-one关系
    # task = db.relationship('DownloadTask', backref='queue_items', lazy=True, cascade='all, delete-orphan')
    
    def to_dict(self):
        return {
            'id': self.id,
            'task_id': self.task_id,
            'priority': self.priority,
            'retry_count': self.retry_count,
            'max_retries': self.max_retries,
            'status': self.status,
            'error_message': self.error_message,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

